package com.junzhiit.nengji.shop.dao.impl;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.shop.bo.AdvertiseBO;
import com.junzhiit.nengji.shop.bo.ModulesShopsBO;
import com.junzhiit.nengji.shop.bo.ShopModulesBO;
import com.junzhiit.nengji.shop.dao.ModulesShopsDAO;
import com.junzhiit.nengji.shop.model.ModulesShops;
import com.junzhiit.nengji.shop.model.enums.Is;
import com.junzhiit.nengji.shop.model.enums.ShopIsPlatform;
import com.junzhiit.nengji.shop.model.enums.Status;

@Repository
public class ModulesShopsDAOImpl extends AbstractBaseDAO<ModulesShops> implements ModulesShopsDAO {

	@Override
	public List<ModulesShopsBO> findAll(int pageNumber, int maxPageRow,ModulesShopsBO msb) {
		StringBuffer sql=new StringBuffer();
		StatementParameter params=new StatementParameter();
		sql.append("select");
		sql.append(" s.*");
		sql.append(" from");
		sql.append(" shop s");
		sql.append(" inner join modules_shops ms on s.id=ms.cs_id");
		sql.append(" inner join modules m on ms.modules_id=m.id");
		sql.append(" where s.is_delete=? and s.is_platform=? and ms.modules_id=? and m.is_open_page=?");
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(ShopIsPlatform.HEADQUARTERS.toIntStatus());
		params.setString(msb.getModulesId());
		params.setInt(msb.getIsOpenPage());
		if(StringUtils.isNoneBlank(msb.getName())){
			sql.append(" and s.name like ?");
			params.setString("%"+msb.getName()+"%");
		}
		if(msb.getStatus()!=null){
			sql.append(" and s.status=?");
			params.setInt(msb.getStatus());
		}
		return super.queryForPagingList(sql.toString(), ModulesShopsBO.class, params, pageNumber, maxPageRow);
	}

	@Override
	public int getTotalCountByAll(ModulesShopsBO msb) {
		StringBuffer sql=new StringBuffer();
		StatementParameter params=new StatementParameter();
		sql.append("select");
		sql.append(" count(*)");
		sql.append(" from");
		sql.append(" shop s");
		sql.append(" inner join modules_shops ms on s.id=ms.cs_id");
		sql.append(" inner join modules m on ms.modules_id=m.id");
		sql.append(" where s.is_delete=? and s.is_platform=? and ms.modules_id=? and m.is_open_page=?");
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(Is.NO.toIntValue());
		params.setString(msb.getModulesId());
		params.setInt(msb.getIsOpenPage());
		if(StringUtils.isNoneBlank(msb.getName())){
			sql.append(" and s.name=?");
			params.setString(msb.getName());
		}
		if(msb.getStatus()!=null){
			sql.append(" and s.status=?");
			params.setInt(msb.getStatus());
		}
		return super.queryForInt(sql.toString(), params);
	}

	@Override
	public int getMaxSort(ModulesShopsBO msb) {
		StringBuffer sql=new StringBuffer();
		StatementParameter param=new StatementParameter();
		sql.append("select");
		sql.append(" ifnull(max(ms.sort),0)");
		sql.append(" from modules_shops ms");
		sql.append(" where ms.is_delete=? and ms.modules_id=?");
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(msb.getModulesId());
		return super.queryForInt(sql.toString(), param);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public int deleteByCsId(ModulesShopsBO msb) {
		StringBuffer sql=new StringBuffer();
		StatementParameter param=new StatementParameter();
		sql.append("delete from modules_shops where is_delete=? and cs_id=?");
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(msb.getCsId());
		return super.merge(sql.toString(), param);
	}

	@Override
	public List<ShopModulesBO> getShopByColumnCode(String locations,String coordsys,String columnCode, String modulesCode, int pageNumber,
			int maxPageRow) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT");
		sql.append("	ue.score,");
		sql.append("	sp.id AS shopId,");
		sql.append("	sp.`name`,");
		sql.append("	sp.shop_head_img_url,");
		sql.append("	sp.address,");
		sql.append("	sqrt(");
		sql.append("		pow(");
		sql.append("			111.2 * (lat - ?),");
		sql.append("			2");
		sql.append("		) + pow(");
		sql.append("			111.2 * (? - lng) * cos(lat / 57.3),");
		sql.append("			2");
		sql.append("		)");
		sql.append(") AS distance");
		sql.append(" FROM");
		sql.append("	(");
		sql.append("		SELECT");
		sql.append("			mg.shop_id,");
		sql.append("			IFNULL(AVG(ue.score), 0) score");
		sql.append("		FROM");
		sql.append("			(");
		sql.append("				SELECT");
		sql.append("					mg.cs_id shop_id");
		sql.append("				FROM");
		sql.append("					(");
		sql.append("						SELECT");
		sql.append("							m.id,");
		sql.append("							m. NAME modules_name,");
		sql.append("							m. CODE modules_code");
		sql.append("						FROM");
		sql.append("							(");
		sql.append("								SELECT");
		sql.append("									id,");
		sql.append("									`name`");
		sql.append("								FROM");
		sql.append("									`column`");
		sql.append("								WHERE");
		sql.append("									`code` = ?");
		sql.append("								AND IFNULL(shop_id, '') = ''");
		sql.append("							) c");
		sql.append("						JOIN modules m ON c.id = m.column_id");
		sql.append("						AND m.is_delete = 0");
		sql.append("						AND m. CODE = ?");
		sql.append("					) m");
		sql.append("				JOIN modules_shops mg ON m.id = mg.modules_id");
		sql.append("				WHERE");
		sql.append("					mg.is_delete = 0");
		sql.append("				AND mg. STATUS != 2");
		sql.append("			) mg");
		sql.append("		LEFT JOIN user_evaluate ue ON mg.shop_id = ue.shop_id");
		sql.append("		GROUP BY");
		sql.append("			mg.shop_id");
		sql.append("	) ue");
		sql.append(" JOIN shop sp ON sp.id = ue.shop_id");
		sql.append(" LIMIT ?,?");
		StatementParameter params = new StatementParameter();
		params.setString(locations);
		params.setString(coordsys);
		params.setString(columnCode);
		params.setString(modulesCode);
		params.setInt(pageNumber);
		params.setInt(maxPageRow);
		return super.queryForList(sql.toString(), ShopModulesBO.class,params);
	}

	@Override
	public int getShopByColumnCodeTotalCount(String locations,String coordsys,String columnCode, String modulesCode) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT");
		sql.append("	count(*)");
		sql.append(" FROM");
		sql.append("	(");
		sql.append("		SELECT");
		sql.append("			mg.shop_id,");
		sql.append("			IFNULL(AVG(ue.score), 0) score");
		sql.append("		FROM");
		sql.append("			(");
		sql.append("				SELECT");
		sql.append("					mg.cs_id shop_id");
		sql.append("				FROM");
		sql.append("					(");
		sql.append("						SELECT");
		sql.append("							m.id,");
		sql.append("							m. NAME modules_name,");
		sql.append("							m. CODE modules_code");
		sql.append("						FROM");
		sql.append("							(");
		sql.append("								SELECT");
		sql.append("									id,");
		sql.append("									`name`");
		sql.append("								FROM");
		sql.append("									`column`");
		sql.append("								WHERE");
		sql.append("									`code` = ?");
		sql.append("								AND IFNULL(shop_id, '') = ''");
		sql.append("							) c");
		sql.append("						JOIN modules m ON c.id = m.column_id");
		sql.append("						AND m.is_delete = 0");
		sql.append("						AND m. CODE = ?");
		sql.append("					) m");
		sql.append("				JOIN modules_shops mg ON m.id = mg.modules_id");
		sql.append("				WHERE");
		sql.append("					mg.is_delete = 0");
		sql.append("				AND mg. STATUS != 2");
		sql.append("			) mg");
		sql.append("		LEFT JOIN user_evaluate ue ON mg.shop_id = ue.shop_id");
		sql.append("		GROUP BY");
		sql.append("			mg.shop_id");
		sql.append("	) ue");
		sql.append(" JOIN shop sp ON sp.id = ue.shop_id");
		StatementParameter params = new StatementParameter();
		params.setString(columnCode);
		params.setString(modulesCode);
		return super.queryForInt(sql.toString(),params);
	}

	@Override
	public List<AdvertiseBO> getAdvertiseByCode(String modulesCode) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT");
		sql.append("	adv.advertise_url,");
		sql.append("	adv.img_url");
		sql.append(" FROM");
		sql.append("	(");
		sql.append("		SELECT");
		sql.append("			id");
		sql.append("		FROM");
		sql.append("			modules");
		sql.append("		WHERE");
		sql.append("			CODE = ?");
		sql.append("		AND IFNULL(shop_id, '') = ''");
		sql.append("	) m");
		sql.append(" JOIN modules_advertise mad ON mad.modules_id = m.id");
		sql.append(" JOIN advertise adv ON adv.id = mad.advertise_id");
		sql.append(" AND adv.is_delete = ?");
		sql.append(" AND adv.`status` = ?");
		StatementParameter params = new StatementParameter();
		params.setString(modulesCode);
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(Status.ACTIVE.toIntStatus());
		return super.queryForList(sql.toString(), AdvertiseBO.class,params);
	}
}

